package com.iflysse.dao;

import com.iflysse.pojo.po.common.CommonPoResp;
import com.iflysse.pojo.po.req.StudentIdAndTaskIdPoReq;
import com.iflysse.pojo.po.req.TaskDetailPoReq;
import com.iflysse.pojo.po.req.TaskPoReq;
import com.iflysse.pojo.po.req.TaskStatePoReq;
import com.iflysse.pojo.po.resp.StudentTaskPoResp;
import com.iflysse.pojo.po.resp.TaskDetailPoResp;
import com.iflysse.pojo.po.resp.TaskPoResp;
import com.iflysse.pojo.po.resp.TaskResourceCompletePoResp;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.util.List;

/**
 * 任务dao
 */
@Repository
public interface TaskDao {


    /**
     * 根据任务编号,删除任务
     * @param testId
     * @return
     */
    @Update("update tb_task set is_delete = 1 where task_id =#{testId}")
    int deleteTaskByTaskId(String testId) throws SQLException;





    /**
     * 改变任务的状态
     * @param testStatePoReq
     * @return
     * @throws SQLException
     */
    @Update(" update tb_task set task_state = #{state.stateId} where task_id = #{taskId}")
    int updateTaskState(TaskStatePoReq testStatePoReq) throws SQLException;




    /**
     * 添加一个任务
     * @param taskPoReq
     * @return
     */
    @Insert({"insert into tb_task (task_id,teaching_class_id,task_name,task_remark," +
            "task_state,update_time,task_expiration_date,resource_number_of_task) " +
            "values(#{taskId},#{teachingClassId},#{taskName},#{taskRemark},#{state.stateId},now(),#{taskExpirationDate},#{resourceNumberOfChapter})" })
    @Options(useGeneratedKeys = true, keyProperty = "task_id")
    int addTask(TaskPoReq taskPoReq) throws SQLException;

    /**
     * 添加一个任务的详情,包含了哪些章
     * @param taskDetailPoReq
     * @return
     */
    @Insert({
            "<script>"+
                    "insert into tb_task_detail (task_detail_id,task_id,chapter_id)  values  "+
                    "<foreach collection='chapterIdList' item='item' index='index' separator=','>"+
                    "(uuid(),#{taskId},#{item})"+
                    "</foreach>"+
                    "</script>"
    })
    @Options(useGeneratedKeys = true, keyProperty = "task_detail_id")
    int addTaskDetail4Chapter(TaskDetailPoReq taskDetailPoReq) throws SQLException;

    /**
     * 根据班级编号列表,查找对应的学习任务编号,按截至时间倒序排列
     * @param teachingClassIds
     * @param completeTaskIdList
     * @return
     */
    @Select( " <script>" +
            "select task_id from tb_task where is_delete = 0 and teaching_class_id  in  " +
            " <foreach collection='teachingClassIds' open='(' item='questionId' separator=',' close=')'> #{questionId}</foreach> "+
            "<if test='completeTaskIdList.size() > 0'> "+
            "and task_id not in  " +
            "<foreach collection='completeTaskIdList' open='(' item='completeTaskId' separator=',' close=')'> #{completeTaskId}</foreach>"+
            "</if>" +
            "order by TimeStampDiff(MINUTE,task_expiration_date,now())"+
            " </script>" )
    List<String> getOrderedWatingTaskIdsByTeachingIds(List<String> teachingClassIds, List<String> completeTaskIdList) throws SQLException;

    /**
     * 根据学生编号,查找该学生已经完成的任务编号列表
     * @param studentId
     * @return
     */
    @Select({"select task_id from tb_task_student where is_delete = 0 and state = 1 and student_id = #{studentId}"})
    List<String> getCompleteTaskIdsByStudentId(String studentId) throws SQLException;


    /**
     * 根据完成的任务列表,查询task表中的编号,并排序,主要是排序
     * @param completeTaskIdList
     * @return
     */
    @Select( " <script>" +
            "select task_id from tb_task where is_delete = 0 and task_id  in  " +
            " <foreach collection='completeTaskIdList' open='(' item='completeTaskId' separator=',' close=')'> #{completeTaskId}</foreach> "+
            "order by TimeStampDiff(MINUTE,task_expiration_date,now())"+
            " </script>" )
    public List<String> getOrderedCompleteTaskIdsByStudentId(List<String> completeTaskIdList) throws SQLException;

    /**
     * 根据任务编号获取前4个任务列表
     * @param orderedWatingTaskIds
     * @return
     */
    @Select( "<script>" +
            "select task_id,teaching_class_id,task_name,resource_number_of_task,number_of_complete,task_expiration_date from tb_task where task_id in  " +
            " <foreach collection='orderedWatingTaskIds' open='(' item='orderedWatingTaskId' separator=',' close=')'> #{orderedWatingTaskId}</foreach> "+
            "order by TIMESTAMPDIFF(MINUTE,task_expiration_date,now())" +
            "limit 0,4" +
            " </script>" )
    @Results({
            @Result(property = "taskId",column = "task_id"),
            @Result(property = "taskName",column = "task_name"),
            @Result(property = "resourceNumberOfTask",column = "resource_number_of_task"),
            @Result(property = "numberOfComplete",column = "number_of_complete"),
            @Result(property = "taskExpirationDate",column = "task_expiration_date"),
            @Result(property = "teachingClassId",column = "teaching_class_id"),
    })
    List<TaskPoResp> getTop4OrderedWatingTaskByTaskIds(List<String> orderedWatingTaskIds) throws SQLException;
    /**
     * 根据任务编号获取前4个任务列表
     * @param orderedWatingTaskIds
     * @param pageIndex 当前页数
     * @param pageSize 每页条数 默认12条
     * @return
     */
    @Select( "<script>" +
            "select task_id,teaching_class_id,task_name,resource_number_of_task,number_of_complete,task_expiration_date from tb_task where task_id in  " +
            " <foreach collection='orderedWatingTaskIds' open='(' item='orderedWatingTaskId' separator=',' close=')'> #{orderedWatingTaskId}</foreach> "+
            "order by TIMESTAMPDIFF(MINUTE,task_expiration_date,now())" +
            "<bind name='start' value='(pageIndex-1)*pageSize'></bind>" +
            "limit #{start},#{pageSize}" +
            " </script>" )
    @Results({
            @Result(property = "taskId",column = "task_id"),
            @Result(property = "taskName",column = "task_name"),
            @Result(property = "resourceNumberOfTask",column = "resource_number_of_task"),
            @Result(property = "numberOfComplete",column = "number_of_complete"),
            @Result(property = "taskExpirationDate",column = "task_expiration_date"),
            @Result(property = "teachingClassId",column = "teaching_class_id"),
    })
    List<TaskPoResp> getPagedOrderedWatingTaskByTaskIds(List<String> orderedWatingTaskIds, int pageIndex, int pageSize) throws SQLException;


    /**
     * 根据任务编号列表获取章编号列表
     * @param taskId
     * @return
     */
    @Select("select chapter_id from tb_task_detail where task_id = #{taskId}")
    List<String> getChapterIdsByTaskIds(String taskId) throws SQLException;


    /**
     * 根据学生编号和任务编号获取学习资源数量
     * @param studentIdAndTaskIdPoReq
     * @return
     * @throws SQLException
     */
    @Select("select have_learn_number from tb_task_student where task_id = #{taskId} and student_id = #{studentId}")
    Integer getLearnedResourceNumberByStudentIdAndTaskId(StudentIdAndTaskIdPoReq studentIdAndTaskIdPoReq) throws SQLException;

    /**
     * 根据教学班级编号获取教学班级下面的所有测试任务
     * @param teachingClassIds
     * @return
     */
    @Select( "<script>" +
            "select test_id from tb_test where teaching_class_id  in  " +
            " <foreach collection='teachingClassIds' open='(' item='teachingClassId' separator=',' close=')'> #{teachingClassId}</foreach> " +
            "</script>" )
    List<String> getAllTaskByTeachingClassIds(List<String> teachingClassIds) throws SQLException;

    /**
     * 更新学生学习任务中学习资源的数量
     * @param taskId
     * @param studentId
     * @return
     */
    @Update("update tb_task_student set have_learn_number = have_learn_number+1 where task_id =#{taskId} and student_id = #{studentId}")
    int updateLearnResourceAmountByTaskIdAndStudentId(String taskId, String studentId) throws SQLException;


    /**
     * 根据任务编号获取需要学习的资源数
     * @param taskId
     * @return
     */
    @Select({"select resource_number_of_task from tb_task where task_id = #{taskId}"})
    int getTaskResourceAmountByTaskId(String taskId) throws SQLException;

    /**
     * 更新学生任务的完成和过期状态
     * @param taskId 任务编号
     * @param studentId 学生编号
     * @param state 状态 1:完成,2:过期
     * @return
     */
    @Update("update tb_task_student set state = #{state},complete_time = now() where task_id =#{taskId} and student_id = #{studentId}")
    int  updateStudentTaskStateByTaskIdAndStudentId(String taskId,String studentId,int state) throws SQLException;

    /**
     * 更新任务的完成人数
     * @param taskId
     * @return
     */
    @Update("update tb_task set number_of_complete = number_of_complete+1 where task_id =#{taskId}")
    int updateTaskCompleteNumberByTaskId(String taskId) throws SQLException;

    /**
     * 根据任务编号获取任务详情
     * @param taskId
     * @return
     */
    @Select("select * from tb_task where task_id = ?")
    @Results({
            @Result(property = "taskId",column = "task_id"),
            @Result(property = "taskName",column = "task_name"),
            @Result(property = "resourceNumberOfTask",column = "resource_number_of_task"),
            @Result(property = "numberOfComplete",column = "number_of_complete"),
            @Result(property = "taskExpirationDate",column = "task_expiration_date"),
            @Result(property = "teachingClassId",column = "teaching_class_id"),
            @Result(property = "updateTime",column = "update_time"),
            @Result(property = "numberOfStudent",column = "number_of_student"),
})
    TaskDetailPoResp getTaskDetailByTaskId(String taskId) throws SQLException;


    /**
     * 获取某任务的过期人数
     * @param taskId
     * @return
     */
    @Select("select count(*) number from vw_task_student_expiration_date where task_id = #{taskId} and state = 0 and TIMESTAMPDIFF(SECOND,task_expiration_date,now()) > 0")
    int getNumberOfOverdueByTaskId(String taskId) throws SQLException;

    /**
     * 根据任务编号获取学生任务
     * @param taskId 任务编号
     * @return
     */
    @Select("select tts.*,ts.student_name,ts.number,tt.resource_number_of_task,tt.task_expiration_date from tb_task_student tts " +
            "inner join tb_student ts on tts.student_id = ts.student_id " +
            "inner join tb_task tt on tts.task_id = tt.task_id " +
            "where tt.task_id = #{taskId}")
    @Results({
            @Result(property = "studentName",column = "student_name"),
            @Result(property = "studentId",column = "student_id"),
            @Result(property = "haveLearnNumber",column = "have_learn_number"),
            @Result(property = "resourceNumberOfTask",column = "resource_number_of_task"),
            @Result(property = "completeTime",column = "complete_time"),
            @Result(property = "taskExpirationDate",column = "task_expiration_date"),
    })
    List<StudentTaskPoResp> getStudentTaskByTaskId(String taskId) throws SQLException;


    /**
     * 根据任务编号,获取任务下的资源学生的完成情况
     * @param taskId
     * @return
     */
    @Select("SELECT resource_title resourceTitle," +
            "(SELECT count(*) from tb_task_learn_record ttlr " +
            "where ttlr.task_id = ttd.task_id and  ttlr.resource_id = tcr.resource_id ) studentCompleteNumber " +
            "from tb_task_detail ttd " +
            "INNER JOIN tb_chapter_resource  tcr " +
            "on ttd.chapter_id = tcr.chapter_id " +
            " where task_id =#{taskId}")
    @Results({
            @Result(property = "studentName",column = "student_name"),
            @Result(property = "studentId",column = "student_id"),
            @Result(property = "haveLearnNumber",column = "have_learn_number"),
            @Result(property = "resourceNumberOfTask",column = "resource_number_of_task"),
            @Result(property = "completeTime",column = "complete_time"),
            @Result(property = "taskExpirationDate",column = "task_expiration_date"),
    })
    List<TaskResourceCompletePoResp> getTaskResourceCompleteByTaskId(String taskId) throws SQLException;

}
